<?php
class Bytouroperators_model extends CRM_Model{
	
	public function __construct(){
		parent::__construct();
	}
	
	public function GetDS(){
		$this->db->select('_touroperators.rid as operatorRid,
							_touroperators.stouroperator_name as operatorName,  
							_countries.country_name as countryName, 
							_countries.rid as countryRid,
							count(DISTINCT _demands_rows.rid) as touristsQuan, 
							sum(DISTINCT _demands_headers.summ) as toursSum, 
							sum(DISTINCT _demands_headers.summ)-sum(DISTINCT _tours.order_summ) as doxod,');
		$this->db->from('_demands_headers');
		$this->db->join('_demands_rows', '_demands_rows._demands_headers_rid = _demands_headers.rid');
		$this->db->join('_tours', '_demands_headers._tours_rid = _tours.rid');
		$this->db->join('_countries', '_tours._countries_rid = _countries.rid');
		$this->db->join('_touroperators', '_tours._touroperators_rid = _touroperators.rid');
		$this->db->join('_demandsstatuses', '_demands_headers._demandsstatuses_rid = _demandsstatuses.rid');		
		$this->db->where(array('_demands_headers.date_doc >='=>$this->toMySQLDate($_POST['bdate']), 
								'_demands_headers.date_doc <='=>$this->toMySQLDate($_POST['edate']),
								'_demandsstatuses.code'=>'000013'));
		$this->db->groupby('_touroperators.rid, _countries.rid');
		$query = $this->db->get();
		if($query->num_rows()>0) return $query->result();
		return array();
	}
	
	public function to_Excel(){
		$xls =& new Spreadsheet_Excel_Writer(); 
		$xls->send("bytouroperators.xls"); 
		$sheet =& $xls->addWorksheet('bytouroperators');
		$titleFormat =& $xls->addFormat();
		$titleFormat->setFontFamily('Helvetica');
		$titleFormat->setBold();
		$titleFormat->setSize('11');
		
		$textFormat =& $xls->addFormat();
		$textFormat->setFontFamily('Helvetica');
		$textFormat->setSize('10');
		
		$titleText = iconv('UTF-8', 'Windows-1251', "Отчет по туроператорам за {$_POST['bdate']} - {$_POST['edate']}");
		$sheet->write(0,0,$titleText,$titleFormat);
		$currRow = 2;
		$ds = $this->GetDS();
		$totalsByTouroperators = array();
		$allTotals = array('touristsQuan'=>0, 'toursSum'=>0, 'doxod'=>0);
		$totalsByCountries = array();
		foreach($ds as $row){
			if(!isset($totalsByTouroperators[$row->operatorRid])) $totalsByTouroperators[$row->operatorRid] = array('touristsQuan'=>0, 'toursSum'=>0, 'doxod'=>0);
			if(!isset($totalsByCountries[$row->countryName])) $totalsByCountries[$row->countryName] = array('touristsQuan'=>0, 'toursSum'=>0, 'doxod'=>0);
			$totalsByTouroperators[$row->operatorRid]['touristsQuan'] += $row->touristsQuan;
			$totalsByTouroperators[$row->operatorRid]['toursSum'] += $row->toursSum;
			$totalsByTouroperators[$row->operatorRid]['doxod'] += $row->doxod;
			$totalsByCountries[$row->countryName]['touristsQuan'] += $row->touristsQuan;
			$totalsByCountries[$row->countryName]['toursSum'] += $row->toursSum;
			$totalsByCountries[$row->countryName]['doxod'] += $row->doxod;
			$allTotals['touristsQuan'] += $row->touristsQuan;
			$allTotals['toursSum'] += $row->toursSum;
			$allTotals['doxod'] += $row->doxod;
		}
		$currTouroperatorRid = null;
		foreach($ds as $row){
			if($row->operatorRid != $currTouroperatorRid){
				// если текущий туроператор не null то выводим итоги
				if($currTouroperatorRid){
					$sheet->write($currRow,0,iconv('UTF-8', 'Windows-1251', 'Итого'), $titleFormat);
					$sheet->write($currRow, 2, '-', $textFormat);
					$sheet->write($currRow, 3, $totalsByTouroperators[$row->operatorRid]['touristsQuan'], $textFormat);
					$sheet->write($currRow, 4, $totalsByTouroperators[$row->operatorRid]['toursSum'], $textFormat);
					$sheet->write($currRow, 5, $totalsByTouroperators[$row->operatorRid]['doxod'], $textFormat);
					$sheet->write($currRow, 6, $totalsByTouroperators[$row->operatorRid]['doxod']/($allTotals['doxod']/100), $textFormat);
					$currRow++;
				}
				// 
				$sheet->write($currRow,0,iconv('UTF-8', 'Windows-1251', $row->operatorName), $titleFormat);
				$currRow++;
				// Отображаем хидер
				$sheet->write($currRow, 1,iconv('UTF-8', 'Windows-1251', 'Страна'), $titleFormat);
				$sheet->write($currRow, 2,iconv('UTF-8', 'Windows-1251', 'Комиссия'), $titleFormat);
				$sheet->write($currRow, 3,iconv('UTF-8', 'Windows-1251', 'К-во туристов'), $titleFormat);
				$sheet->write($currRow, 4,iconv('UTF-8', 'Windows-1251', 'Сума'), $titleFormat);
				$sheet->write($currRow, 5,iconv('UTF-8', 'Windows-1251', 'Доход'), $titleFormat);
				$sheet->write($currRow, 6,iconv('UTF-8', 'Windows-1251', 'Доля'), $titleFormat);
				$currRow++;
				$currTouroperatorRid = $row->operatorRid;
			}
			$sheet->write($currRow, 1,iconv('UTF-8', 'Windows-1251', $row->countryName), $textFormat);
			$sheet->write($currRow, 2, '-', $textFormat);
			$sheet->write($currRow, 3, $row->touristsQuan, $textFormat);
			$sheet->write($currRow, 4, $row->toursSum, $textFormat);
			$sheet->write($currRow, 5, $row->doxod, $textFormat);
			$sheet->write($currRow, 6, $row->doxod/($totalsByTouroperators[$row->operatorRid]['doxod']/100), $textFormat);
			$currRow++;
		}
		if($currTouroperatorRid){
			$sheet->write($currRow,0,iconv('UTF-8', 'Windows-1251', 'Итого'), $titleFormat);
			$sheet->write($currRow, 2, $totalsByTouroperators[$currTouroperatorRid]['doxod']/($allTotals['toursSum']/100), $textFormat);
			$sheet->write($currRow, 3, $totalsByTouroperators[$currTouroperatorRid]['touristsQuan'], $textFormat);
			$sheet->write($currRow, 4, $totalsByTouroperators[$currTouroperatorRid]['toursSum'], $textFormat);
			$sheet->write($currRow, 5, $totalsByTouroperators[$currTouroperatorRid]['doxod'], $textFormat);
			$sheet->write($currRow, 6, $totalsByTouroperators[$currTouroperatorRid]['doxod']/($allTotals['doxod']/100), $textFormat);
			$currRow++;
		}
		$currRow++;
		$sheet->write($currRow, 0, iconv('UTF-8', 'Windows-1251', 'Всего'), $titleFormat);
		$sheet->write($currRow, 2, $allTotals['doxod']/($allTotals['toursSum']/100), $textFormat);
		$sheet->write($currRow, 3, $allTotals['touristsQuan'], $textFormat);
		$sheet->write($currRow, 4, $allTotals['toursSum'], $textFormat);
		$sheet->write($currRow, 5, $allTotals['doxod'], $textFormat);
		$currRow++;
		foreach($totalsByCountries as $key=>$row){
			$sheet->write($currRow, 1, iconv('UTF-8', 'Windows-1251', $key), $textFormat);
			$sheet->write($currRow, 3, $row['touristsQuan'], $textFormat);
			$sheet->write($currRow, 4, $row['toursSum'], $textFormat);
			$sheet->write($currRow, 5, $row['doxod'], $textFormat);
			$sheet->write($currRow, 6, $row['doxod']/($allTotals['doxod']/100), $textFormat);
			$currRow++;
		}
		$xls->close(); 
	}
}
?>